Electronic storage mechanisms have enabled accumulation of massive amounts of data. For instance, data that previously required volumes of books for recordation can now be stored electronically without expense of printing paper and with a fraction of physical space needed for storage of paper. Database systems are often employed for storage and organization of data, wherein such databases can be queried by users to retrieve desirable data. Database systems have been widely deployed and applications associated therewith have become increasingly complex and varied.
Complex queries are common in decision support and reporting scenarios. Query optimization tends to be expensive for such complex queries despite development of techniques to cope with such queries. Therefore, many database systems use a plan cache for compiled query execution plans, which helps to mitigate the overhead of repeated recompilation for the most frequently executed queries.
Even if a plan is resident in the cache, it is invalidated by changes to the statistics (or other information) of the base data or through changes in the physical database design such as addition or deletion of an index or changes to other relevant metadata in the system. This is common since changes in statistics on base data and addition of indexes can change the optimal plan selected by an optimizer. Further, removal of indexes used in a plan renders the current plan not executable. Once a plan is invalidated, the query must be optimized again to generate a new query plan. Although plan invalidation and consequent recompilation can be motivated for reasons associated with integrity of the base data, they can lead to a significant expense due to the time and costs of recompilation for complex queries.